# _*_ coding:utf-8 _*_
# @File  : 仓单数据处理入库.py
# @Time  : 2021-10-08  13:56
# @Author: zizle
import datetime
import os
import time
import pandas as pd

from spiders import futures_daily_receipt as fdr
from db import FAConnection


# 先生成日期
def date_generator(start, end):
    start_date = datetime.datetime.strptime(start, '%Y-%m-%d')
    end_date = datetime.datetime.strptime(end, '%Y-%m-%d')
    while start_date <= end_date:
        yield start_date.strftime('%Y%m%d')
        start_date += datetime.timedelta(days=1)


# 处理郑商所数据
def handler_czce(receipt_date):
    receipt_handler = fdr.CZCEDailyReceipt(receipt_date)
    receipt_handler.filepath = f'temp/czce/receipt/{receipt_date.strftime("%Y-%m-%d")}.xls'
    # 文件不存在，则跳过
    if not os.path.exists(receipt_handler.filepath):
        print(f'无{receipt_date.strftime("%Y-%m-%d")}郑商所仓单!')
        return
    receipt_data, _ = receipt_handler.get_daily_receipt()
    save_receipt(receipt_data, '郑商所', receipt_date.strftime('%Y%m%d'))


# 处理大商所数据
def handler_dce(receipt_date):
    receipt_handler = fdr.DCEDailyReceipt(receipt_date)
    receipt_handler.filepath = f'temp/dce/receipt/{receipt_date.strftime("%Y-%m-%d")}.html'
    # 文件不存在，则跳过
    if not os.path.exists(receipt_handler.filepath):
        print(f'无{receipt_date.strftime("%Y-%m-%d")}大商所仓单!')
        return
    receipt_data, _ = receipt_handler.get_daily_receipt()
    save_receipt(receipt_data, '大商所', receipt_date.strftime('%Y%m%d'))


# 处理上期所数据
def handler_shfe(receipt_date):
    receipt_handler = fdr.SHFEDailyReceipt(receipt_date)
    receipt_handler.filepath = f'temp/shfe/receipt/{receipt_date.strftime("%Y-%m-%d")}.json'
    # 文件不存在，则跳过
    if not os.path.exists(receipt_handler.filepath):
        print(f'无{receipt_date.strftime("%Y-%m-%d")}上期所所仓单!')
        return
    receipt_data, _ = receipt_handler.get_daily_receipt()
    save_receipt(receipt_data, '上期所', receipt_date.strftime('%Y%m%d'))


# 保存数据
def save_receipt(save_data, ex_name, receipt_date):
    df = pd.DataFrame(save_data)
    ts = int(datetime.datetime.strptime(receipt_date, '%Y%m%d').timestamp())
    df['receipt_ts'] = [ts for _ in range(df.shape[0])]
    df['creator'] = [1 for _ in range(df.shape[0])]
    # 去除0仓单的数据
    df['receipt'] = df['receipt'].apply(float)
    df['increase'] = df['increase'].apply(float)
    df = df[(df['receipt'] != 0) | (df['increase'] != 0)]
    df['warehouse'] = df['warehouse'].apply(lambda x: x.strip())
    final_data = df.to_dict(orient='records')
    # 保存数据
    save_sql = 'INSERT IGNORE INTO dat_futures_daily_receipt (creator,receipt_ts,variety_en,warehouse,receipt,' \
               'increase,ex_total) ' \
               'VALUES (%(creator)s,%(receipt_ts)s,%(variety_en)s,%(warehouse)s,%(receipt)s,' \
               '%(increase)s,%(ex_total)s);'
    db = FAConnection(conn_name='Save Futures Daily Receipt')
    count, _ = db.insert(save_sql, param=final_data, many=True, keep_conn=True)
    db.correct_table_auto_increment(table_name='dat_futures_daily_receipt', finish_closed=True)
    print(f'保存{receipt_date} - {ex_name} 数据成功! - {count}个。')


if __name__ == '__main__':
    for r_date in date_generator('2021-04-10', '2021-09-30'):
        parser_date = datetime.datetime.strptime(r_date, '%Y%m%d')
        handler_czce(parser_date)
        time.sleep(0.5)
        handler_dce(parser_date)
        time.sleep(0.5)
        handler_shfe(parser_date)
        time.sleep(0.6)


